<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Service_model extends CI_Model { 
    function __construct()
    {
        // Call the Model constructor
        parent::__construct();
    }
    
    public function get_service_by_id($service_id)
    {
        $query = "SELECT * FROM psacar_service where id = " . $service_id;
        $result = $this->db->query($query);
        return  $result;
    }
    
    public function query_get_service()
    {
        return 
            "SELECT 
                member.id as member_id,
                member.name as member_name,
                member.company_phone_1,
                member.company_phone_2,
                member.website,
                member.email,
                member.first_name,
                member.last_name,
                member.contact_phone_1, 
                member.contact_phone_2,
                member.code as member_code,       
                member.address,
                member.number_vehicle,
                member.photo,
                occupation.occupation,
                industry.industry,
                type.name as car_type,
                member_type.member_type,
                service.name as service_name,
                service.description as service_description,
                make.name as make_name,                
                location.province
            FROM    
                psacar_member_service as member_service
                inner join psacar_member as member on member_service.member_id = member.id
                left join psacar_car as car on member.id = car.dealer_id
                -- left join psacar_series series on car.series_id = series.id
                left join psacar_occupation as occupation on member.occupation_id = occupation.id 
                left join psacar_industry as industry on member.industry_id = industry.id                 
                left join psacar_car_type as type on member.car_type = type.id
                left join psacar_member_type as member_type on member.type_id = member_type.id                                
                inner join psacar_service as service on member_service.service_id = service.id
                left join psacar_location as location on member_service.location_id = location.id
                left join psacar_make as make on member_service.make_id = make.id ";
    }
    
    public function get_approved_service_by_member_id($member_id)
    {
        $query_string = 
            $this->query_get_service() . 
            "WHERE
                member_service.status = 1
                AND member_service.expiration_date >= curdate()
                AND member.id = " . $member_id . 
            " ORDER BY service.name";
        $result = $this->db->query($query_string);
        return  $result;        
    }
    
    public function get_pending_service_by_member_id($member_id)
    {
        $query_string = 
            $this->query_get_service() . 
            "WHERE
                member_service.status = 0
                AND member.id = " . $member_id;
                
        $result = $this->db->query($query_string);
        return  $result;        
    }
    
    public function get_expired_service_by_member_id($member_id)
    {
        $query_string = 
            $this->query_get_service() . 
            "WHERE
                member_service.status = 1
                AND member_service.expiration_date < curdate()
                AND member.id = " . $member_id;
                
        $result = $this->db->query($query_string);
        return  $result;        
    }
    
    public function get_service_list(
        $make, 
        $model, 
        $service,
        $location,
        $year) {
        
        $extra = " ";
        $operator = " AND ";
        $this->load->model('Track_model');
        
        if(strlen($make) > 0)
        {            
            $extra .= $operator . "make.id = " . $this->db->escape($make);	

            $this->db->limit(1);
            $query = $this->db->get_where("psacar_make", array("id"=> $make));
            $make = $query->row();            
            $this->Track_model->track_activity("Make", $make->name);
        }
        
        if(strlen($model) > 0)
        {
            $extra .= $operator . "model.id = " . $this->db->escape($model);

            $this->db->limit(1);
            $query = $this->db->get_where("psacar_model", array("id"=> $model));
            $model = $query->row();                        
            $this->Track_model->track_activity("Model", $model->name);
        }
        
        if(strlen($year) > 0)
        {
            $extra .= $operator . "car.year = " . $this->db->escape($year);
            $this->Track_model->track_activity("Year", $year);
        }

        if(strlen($service) > 0)
        {
            $extra .= $operator . "service.name = " . $this->db->escape($service);
            $this->Track_model->track_activity("Service", $service);
        }

        if(strlen($location) > 0)
        {
            $extra .= $operator . "location.province = " . $this->db->escape($location);
            $this->Track_model->track_activity("Location", $location);
        }

        $query_string = 
            $this->query_get_service() . 
            "WHERE
                member_service.status = 1
                AND member_service.expiration_date >= curdate()" . $extra . 
            " ORDER BY service.name";
        //echo $query_string;
        
        $result = $this->db->query($query_string);
        return  $result;
    }
}